Stored Procedures and Triggers in SQL

Published 3 months ago

Introduction

SQL (Structured Query Language) provides powerful mechanisms for automating tasks and improving database efficiency. Among these mechanisms, Stored Procedures and Triggers play a crucial role in handling repetitive tasks, maintaining data integrity, and ensuring business logic enforcement within a database.

In this article, we will explore:

What Stored Procedures are and how to create and use them.
Understanding Triggers and their importance.
Using Triggers for automation.

What are Stored Procedures?

A Stored Procedure is a precompiled set of one or more SQL statements that can be executed as a single unit. These procedures are stored in the database and can be reused multiple times, reducing redundancy and improving performance.

Benefits of Stored Procedures

Improved Performance: Since stored procedures are compiled once and stored in the database, they execute faster than ad hoc SQL queries.
Code Reusability: Procedures can be reused across different applications and scripts.
Security: Permissions can be granted to execute the procedure without allowing access to the underlying tables.
Reduced Network Traffic: Instead of sending multiple queries, a single call to a stored procedure reduces data transmission.

Creating and Using Stored Procedures

To create a stored procedure, use the CREATE PROCEDURE statement.

Syntax:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL Statements
END;

Example: Creating a Stored Procedure

Let's create a stored procedure that retrieves all employees from the employees table:

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM employees;
END;

Executing a Stored Procedure

To execute a stored procedure, use the EXEC or CALL statement:

EXEC GetAllEmployees;

CALL GetAllEmployees;

Stored Procedure with Parameters

Stored procedures can accept input parameters to filter data dynamically.

CREATE PROCEDURE GetEmployeeByID (@EmpID INT)
AS
BEGIN
    SELECT * FROM employees WHERE EmployeeID = @EmpID;
END;

To execute:

EXEC GetEmployeeByID 101;

Understanding Triggers

A Trigger is a special kind of stored procedure that automatically executes when a specified event occurs in the database. Triggers are mainly used to enforce business rules, maintain audit logs, or prevent invalid transactions.

Types of Triggers

BEFORE Triggers: Execute before an INSERT, UPDATE, or DELETE operation.
AFTER Triggers: Execute after an INSERT, UPDATE, or DELETE operation.
INSTEAD OF Triggers: Replace the execution of an INSERT, UPDATE, or DELETE operation.

Benefits of Using Triggers

Automatic Execution: Triggers are fired automatically when defined events occur.
Data Integrity Enforcement: Ensures that the database remains consistent by enforcing constraints.
Audit Trail: Keeps track of changes made to the database tables.

Creating a Trigger

Example: Logging Changes in Employee Table

We want to maintain a log whenever an employee’s salary is updated.

CREATE TABLE EmployeeSalaryLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10,2),
    NewSalary DECIMAL(10,2),
    ChangeDate DATETIME DEFAULT GETDATE()
);

CREATE TRIGGER trg_UpdateSalary
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeSalaryLog (EmployeeID, OldSalary, NewSalary)
    SELECT i.EmployeeID, d.Salary, i.Salary
    FROM inserted i
    JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary <> d.Salary;
END;

This trigger logs salary changes whenever an update occurs in the employees table.

Using Triggers for Automation

Triggers can be used for various automation tasks, such as:

1. Enforcing Business Rules

For instance, preventing employees from being deleted if they have pending tasks:

CREATE TRIGGER trg_PreventEmployeeDelete
ON employees
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM tasks WHERE EmployeeID IN (SELECT EmployeeID FROM deleted))
    BEGIN
        RAISERROR ('Cannot delete employee with pending tasks', 16, 1);
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        DELETE FROM employees WHERE EmployeeID IN (SELECT EmployeeID FROM deleted);
    END;
END;

2. Automatic Backup of Deleted Records

To maintain an archive of deleted records:

CREATE TABLE EmployeeArchive (
    EmployeeID INT, Name VARCHAR(100), Position VARCHAR(50), Salary DECIMAL(10,2), DeletedDate DATETIME DEFAULT GETDATE()
);

CREATE TRIGGER trg_ArchiveEmployee
ON employees
AFTER DELETE
AS
BEGIN
    INSERT INTO EmployeeArchive (EmployeeID, Name, Position, Salary)
    SELECT EmployeeID, Name, Position, Salary FROM deleted;
END;

This trigger ensures that deleted employee records are stored in EmployeeArchive.

Conclusion

Stored Procedures and Triggers are powerful features in SQL that help automate tasks, enforce business rules, and improve database performance. While stored procedures allow us to encapsulate reusable logic, triggers enable automatic execution of predefined actions when specific database events occur.

Key Takeaways

Stored Procedures help execute precompiled SQL statements, improving efficiency and reusability.
Triggers automatically execute when an event occurs, ensuring data consistency and enforcing business logic.
Best Practices:Use stored procedures for complex business logic.
Use triggers sparingly to avoid performance issues.
Always test stored procedures and triggers to ensure they function as expected.

By mastering these features, you can optimise your database operations and enhance data management efficiency.

Obafemi Emmanuel